# -*- coding: utf-8 -*-

'''
Script de criacao BD e insercao dados

@author Diogo Fialho - 5158
@author Samuel Gonçalves - 5312
'''
import sqlite3
import xlrd
import csv


lista_Curso=[]
lista_Instituicao=[]
lista_Grau=[]
lista_Vagas_Inic=[]
lista_Colocados=[]
lista_Notas=[]
lista_Sobrantes=[]
lista_Escola=[]
lista_E=[]
lista_Escola_Curso=[]
listaEscCur=[]
lista_codInst=[]
    
def Nome_Instituicao (data):
    return data.split(" - ")


def Criar_BD():
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    XLS(conn)
    criar_Tabelas(conn)
    inserirDados(cursor,conn)
    conn.close()

#Função para a criação das tbelas    
def criar_Tabelas(conn):
        
        
        conn.execute(''' drop table if exists Instituicao ''')
        conn.execute(''' drop table if exists Escolas ''')
        conn.execute(''' drop table if exists Cursos ''')
        conn.execute(''' drop table if exists Escola_Cursos ''')

        conn.execute(''' create table if not exists Instituicao(CodigoInstituicao integer primary key,
                                                                NomeInstituicao text not null)''')

        conn.execute(''' create table if not exists Escolas(CodigoEscola integer Primary key autoincrement,
                                                                NomeEscola text unique on conflict ignore,
                                                                CodigoInstituicao integer references Instituicao(CodigoInstituicao))''')


        conn.execute(''' create table if not exists Cursos(CodigoCurso text primary key,                                                       
                                                                NomeCurso text not null)''')

        conn.execute(''' create table if not exists Escola_Cursos( id_escola integer references Escolas(CodigoEscola),                                                        
                                                                id_cursos integer references Cursos(CodigoCurso),
                                                                Grau text,
                                                                VagasIniciais integer,
                                                                Colocados integer,
                                                                Nota float,
                                                                VagasSobrantes, integer)''')
            
        
        conn.commit()

 #Função para ler o ficheiro XLS "cna131fresultados", guardando os dados em listas      
def XLS(conn):
    book = xlrd.open_workbook("cna131fresultados.xls")
    sh = book.sheet_by_name('Resultados')
    
    for rownum in range(3,sh.nrows-2):
   
        codigo_instituicao = 0
        codigo_curso = 1
        nome_instituicao = 2
        nome_curso = 3
        grau = 4
        vagas_inicial = 5
        colocados = 6
        notas = 7
        vagas_sobrantes = 8

        data_cod_Instituicao=sh.cell_value(rownum,codigo_instituicao)
        data_cod_Curso=sh.cell_value(rownum,codigo_curso)
        data_Nome_Instituicao=sh.cell_value(rownum,nome_instituicao)
        data_Nome_Curso=sh.cell_value(rownum,nome_curso)
        data_Grau=sh.cell_value(rownum,grau)
        data_Vagas_Iniciais=sh.cell_value(rownum,vagas_inicial)
        data_Colocados=sh.cell_value(rownum,colocados)
        data_Notas=sh.cell_value(rownum,notas)
        data_Vagas_Sobrantes=sh.cell_value(rownum,vagas_sobrantes)
    
    
        lista_Curso.append((data_cod_Curso, data_Nome_Curso))
        lista_Escola_Curso.append((data_cod_Instituicao,data_cod_Curso,data_Grau, data_Vagas_Iniciais, data_Colocados, data_Notas, data_Vagas_Sobrantes))
        lista_Instituicao.append((data_cod_Instituicao, Nome_Instituicao(data_Nome_Instituicao)[0]))
        
        lista_Escola.append(Nome_Instituicao(data_Nome_Instituicao))

    conn.commit() 

def inserirDados(cursor,conn):

    lista_Esc=[]
 
    lista_completa = [x for x in set(lista_Instituicao)]
    lista_completa = filter(lambda x: True if not x == u"" else False, 
                        lista_completa)
       
    conn.executemany('insert into Instituicao (CodigoInstituicao,NomeInstituicao) values(?,?)', lista_completa)
    
    lista_E = filter(lambda x: True if len(x) == 2 else False, 
                                lista_Escola)
    
    for Instituto,NomeEscola in lista_E:
    
        cursor.execute('select * from Instituicao where NomeInstituicao = "{0}"'.
                              format(Instituto.encode('utf-8')))
        
        data = cursor.fetchone()
        cod_Inst=data[0]
        lista_Esc.append((NomeEscola,cod_Inst))
    
    cursor.executemany('insert into Escolas (NomeEscola,CodigoInstituicao) values(?,?)', lista_Esc)
   
    lista_completa_curso = [x for x in set(lista_Curso)]
    
    lista_completa_curso = filter(lambda x: True if not x == u"" else False, 
                     lista_completa_curso)

    cursor.executemany('insert into Cursos (CodigoCurso,NomeCurso) values(?,?)', (lista_completa_curso))


    #lista_completa_Escola_Curso = [x for x in set(lista_Escola_Curso)]

    for id_escola, id_cursos, Grau, VagasIniciais, Colocados, Nota, VagasSobrantes in lista_Escola_Curso:
    
            listaEscCur.append((id_escola,id_cursos,Grau,VagasIniciais,Colocados,Nota,VagasSobrantes))
    cursor.executemany('insert into Escola_Cursos (id_escola, id_cursos, Grau, VagasIniciais, Colocados, Nota, VagasSobrantes) values(?,?,?,?,?,?,?)',(listaEscCur)) 
    conn.commit() 

#Leitura de dados para a estatistica
def loadList():
    lista=[]
    
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    cursor.execute('Select CodigoInstituicao from Instituicao')
    data1=cursor.fetchall() 
               
    cursor.close
    return data1

#Leitura de dados para a estatistica
def loadList_2(i):
    
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    lista1=[]
    cursor.execute('Select Colocados from Escola_Cursos where id_escola=?',i)
    data1=cursor.fetchall()
    
    cursor.close
    return data1

#Leitura de dados para a estatistica
def loadList_3(cod):
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    cursor.execute('Select NomeInstituicao from Instituicao where CodigoInstituicao=?',cod)
    data1=cursor.fetchall()
    
    cursor.close
    return data1

#Leitura de dados para a estatistica
def loadList_4(i):
    
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    lista1=[]
    cursor.execute('Select VagasSobrantes from Escola_Cursos where id_escola=?',i)
    data1=cursor.fetchall()
   
    cursor.close
    return data1

#Leitura de dados para a estatistica
def loadList_5(cod):
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    cursor.execute('Select * from Instituicao where CodigoInstituicao=?',cod)
    data1=cursor.fetchall()
    
    cursor.close
    return data1

#Leitura de dados para a estatistica
def loadList_6(listaTemp): 
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    lista1=[]
    lista2=[]
    
    lista1.append(listaTemp)
    cursor.execute('Select Colocados from Escola_Cursos where id_escola=?',(lista1))
    for i in cursor:
        lista2.append(i)
        
    cursor.close
    return lista2

#Leitura de dados para a estatistica
def loadList_7(listaTemp): 
    conn = sqlite3.connect('BaseDados.db')
    cursor = conn.cursor()
    lista1=[]
    lista2=[]
    
    lista1.append(listaTemp)
    cursor.execute('Select VagasSobrantes from Escola_Cursos where id_escola=?',(lista1))
    for i in cursor:
        lista2.append(i)
        
    cursor.close
    return lista2

#Função para guardar informações para um ficheiro
def writeCsv(filename,lista):
    csvfile=open(filename,'wb')
    csvwritter=csv.writer(csvfile)
    for temp in lista:
        csvwritter.writerow(temp)
